OpenEdge Development:<br />ProDataSets Enhanced query support for ProDataSet buffers

As you have seen from looking at the WHERE-STRING attribute for the Data-Relations and FILL-WHERE-STRING for Data-Sources, Progress generates default queries for the purpose of copying database data into the ProDataSet during a FILL and navigating and filtering that data after the FILL. Note that a query expression, such as WHERE OrderLine.OrderNum = ttOrder.OrderNum, is not usable in a QUERY-PREPARE for a query that is not part of a ProDataSet. In a standard query, you have to construct the where-clause out of multiple strings, one of which evaluates to the value of the field in the parent table at the time the QUERY-PREPARE is done. You then have to re-execute the QUERY-PREPARE and the QUERY-OPEN each time the value changes. For example, look at this stand-alone procedure:

DEFINE VARIABLE hQuery AS HANDLE NO-UNDO. DEFINE TEMP-TABLE ttOrder LIKE Order. CREATE ttOrder. FIND Order WHERE Order.OrderNum = 1. BUFFER-COPY Order TO ttOrder. CREATE QUERY hQuery. hQuery:ADD-BUFFER(BUFFER OrderLine:HANDLE). hQuery:QUERY-PREPARE ("FOR EACH OrderLine WHERE OrderLine.OrderNum = ttOrder.OrderNum").

DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO. 
DEFINE TEMP-TABLE ttOrder LIKE Order. 
CREATE ttOrder. 
FIND Order WHERE Order.OrderNum = 1. 
BUFFER-COPY Order TO ttOrder. 
CREATE QUERY hQuery. 
hQuery:ADD-BUFFER(BUFFER OrderLine:HANDLE). 
hQuery:QUERY-PREPARE 
    ("FOR EACH OrderLine WHERE OrderLine.OrderNum = ttOrder.OrderNum").

This has been a limitation ever since dynamic queries were introduced into the language. Progress cannot parse out ttOrder.OrderNum from the rest of the where-clause and insert the current value of the field. Instead you have to code it this way:

DEFINE VARIABLE hQuery AS HANDLE NO-UNDO. DEFINE TEMP-TABLE ttOrder LIKE Order. CREATE ttOrder. FIND Order WHERE Order.OrderNum = 1. BUFFER-COPY Order TO ttOrder. CREATE QUERY hQuery. hQuery:ADD-BUFFER(BUFFER OrderLine:HANDLE). hQuery:QUERY-PREPARE ("FOR EACH OrderLine WHERE OrderLine.OrderNum = " `+ STRING(ttOrder.OrderNum)).`

DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO. 
DEFINE TEMP-TABLE ttOrder LIKE Order. 
CREATE ttOrder. 
FIND Order WHERE Order.OrderNum = 1. 
BUFFER-COPY Order TO ttOrder. 
CREATE QUERY hQuery. 
hQuery:ADD-BUFFER(BUFFER OrderLine:HANDLE). 
hQuery:QUERY-PREPARE 
    ("FOR EACH OrderLine WHERE OrderLine.OrderNum = " 
          + STRING(ttOrder.OrderNum)).

However, when a query is associated with a buffer that is a member of a ProDataSet, it has enhanced rules for what will compile in its dynamic predicate. As you can see from this example, the predicate of a normal query on a normal buffer is limited to an expression involving exclusively either constants or fields from the buffer’s table itself. But if the buffer is part of a ProDataSet, this restriction is relaxed to allow references to any other member buffers in the ProDataSet. Also, if the buffer is part of a Data-Source, you might be able to do a dynamic FIND on it using the tables in any attached ProDataSet buffer. If the buffer is part of a query that is attached to a Data-Source, you also might be able to do a dynamic QUERY-PREPARE on it, referencing the Data-Source’s ProDataSet target buffers.

This means, for example, that given a ProDataSet for ttOrder and its ttOrderlines, the Data-Source query for populating the ProDataSet temp-table ttOrderline from the OrderLine database table can reference the parent temp-table ttOrder directly. In such a case, Progress automatically generates and manages queries that use the information in the Data-Relations to filter the current set of ttOrderLine records for a ttOrder. Internally, Progress can take advantage of internal buffer references to insert a key value such as ttOrder.OrderNum into the ttOrderline query without having to re-prepare the ttOrderline query each time the selected ttOrder changes. This greatly increases the run time efficiency compared to what you would have to do in 4GL code to re-prepare and re-open a child query each time the parent row changes.

QUERY attribute for Data-Sources, Data-Relations, and buffers

To associate a query with a dynamic Data-Source object, use the QUERY attribute. To disassociate the query and Data-Source object, set this attribute to the Unknown value (?). You can also use the FILL-WHERE-STRING attribute to override the WHERE clause in the query.

For a data-relation object, the QUERY attribute returns the handle to the default dynamic query for a child buffer in the relation. This automatically-generated query expresses the relation between parent and child temp-tables, and lets you navigate the child records. You cannot set this handle nor can you modify the query.

For a buffer object, the QUERY attribute returns the handle to the query currently associated with the buffer (if any). If the buffer does not have an associated query, this attribute returns the Unknown value (?). This attribute is also read-only for a buffer object.

KEYS attribute for buffers

You can also us e the KEYS attribute to return a comma-separated list of key fields for a buffer, using this syntax:


`buffer-handle`:KEYS.

This attribute provides a list of the fields in the unique primary index for a temp-table, and can be useful for assigning those key values to related rows. For a temp-table buffer, the KEYS attribute evaluates to a list of fields in the temp-table’s unique primary index. If it has no unique primary index, then the attribute evaluates to the string ROWID.